<html>
 <head>
  <meta charset="UTF-8">
 </head>
 <body>
  <h3 data-lake-id="sja3r" id="sja3r"><span data-lake-id="u0dcd6a9e" id="u0dcd6a9e" style="color: rgba(25, 26, 31, 0.9)">问题发现</span></h3>
  <p data-lake-id="ua0cd8228" id="ua0cd8228"><span data-lake-id="ua88fd0f5" id="ua88fd0f5">我们有一个定时任务，是做扫表的，但是最近经常出现定时任务扫表处理失败的报警，登录到机器上之后，发现有数据库层面的报错：</span></p>
  <p data-lake-id="u9fb61403" id="u9fb61403"><span data-lake-id="u45208f4e" id="u45208f4e">​</span><br></p>
  <pre lang="java"><code>
Caused by: com.taobao.tddl.common.exception.TddlRuntimeException: ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL] Error occurs when execute on GROUP 'FIN_RISK_XXX_GROUP' ATOM 'cn-zhangjiakou_i-xxxxx_fin_risk_xxx_3028': Sort aborted: Query execution was interrupted More: [http://xxx.alibaba-inc.com/faq/faqByFaqCode.ht
ml?faqCode=XXX-4614]
</code></pre>
  <p data-lake-id="u429491bf" id="u429491bf"><br></p>
  <p data-lake-id="ue32501d5" id="ue32501d5"><span data-lake-id="ud5cda802" id="ud5cda802">以上日志我做了简单的脱敏，其中最重要的就是这句：</span></p>
  <p data-lake-id="u6859b00d" id="u6859b00d"><span data-lake-id="uf28d9183" id="uf28d9183">​</span><br></p>
  <pre lang="java"><code>
Sort aborted: Query execution was interrupted
</code></pre>
  <p data-lake-id="u224300bf" id="u224300bf"><br></p>
  <p data-lake-id="u9693f893" id="u9693f893"><span data-lake-id="u194dfd00" id="u194dfd00">这是一个数据库查询执行过程中的错误信息，通常在数据库系统中会出现。</span><strong><span data-lake-id="ua93c1181" id="ua93c1181">这个错误消息表示数据库查询中的排序操作被中断或终止了。</span></strong></p>
  <p data-lake-id="ub3ee6dd3" id="ub3ee6dd3"><strong><span data-lake-id="u2c98f5fd" id="u2c98f5fd">​</span></strong><br></p>
  <h3 data-lake-id="K85bA" id="K85bA"><span data-lake-id="ub8b53934" id="ub8b53934" style="color: rgba(25, 26, 31, 0.9)">问题排查</span></h3>
  <p data-lake-id="u7de8068e" id="u7de8068e"><br></p>
  <p data-lake-id="u227ae1df" id="u227ae1df"><span data-lake-id="ueb46a1db" id="ueb46a1db">这个问题的发生，一般来说有几个原因：</span></p>
  <p data-lake-id="uedfeab05" id="uedfeab05"><span data-lake-id="u82e03e1f" id="u82e03e1f">​</span><br></p>
  <p data-lake-id="u5a60c9d4" id="u5a60c9d4"><span data-lake-id="u0a7f51a3" id="u0a7f51a3">1、慢SQL导致查询超时，这时候就会为了避免数据库链接被长时间占用而中断此次查询</span></p>
  <p data-lake-id="u92902279" id="u92902279"><span data-lake-id="u03c8e444" id="u03c8e444">​</span><br></p>
  <p data-lake-id="u88e05a87" id="u88e05a87"><span data-lake-id="u343a9dd0" id="u343a9dd0">2、查询被手动终止，数据库管理员手动中止正在执行的查询操作也会出现这个异常</span></p>
  <p data-lake-id="uc107b21c" id="uc107b21c"><span data-lake-id="u3c2511e3" id="u3c2511e3">​</span><br></p>
  <p data-lake-id="udbd370e0" id="udbd370e0"><span data-lake-id="ub33597f7" id="ub33597f7">3、资源不足，查询排序操作可能需要大量的计算和内存资源，如果数据库服务器的资源不足以执行排序操作，那么查询可能会被中断。这可能发生在高负载或资源不足的环境中。</span></p>
  <p data-lake-id="u75ea344f" id="u75ea344f"><br></p>
  <p data-lake-id="ueb26ffae" id="ueb26ffae"><br></p>
  <p data-lake-id="u6cc63676" id="u6cc63676"><span data-lake-id="u372e1f01" id="u372e1f01">主要就是以上这三个原因，接下来我们分析了一下导致失败的SQL 语句，这个语句在上面的ERROR日志打印的同时就已经给打印出来了，我隐藏了一些无关紧要的内容，大致SQL如下：</span></p>
  <p data-lake-id="uc121eb0a" id="uc121eb0a"><span data-lake-id="u6676006f" id="u6676006f">​</span><br></p>
  <pre lang="java"><code>
### The error occurred while setting parameters### SQL: 
select
  business_type_enum,
  product_type_enum,
  subject_id,
  subject_id_enum,
  GROUP_CONCAT(distinct (number) SEPARATOR ',') as risk_case_numbers,
  GROUP_CONCAT(distinct (risk_level_enum) SEPARATOR ',') as risk_level_enums,
from
  fraud_risk_case
WHERE
  product_type_enum = ?
  and risk_case_status_enum = 'DRAFT'
  and subject_id like "23%"
group by
  subject_id_enum,
  subject_id
limit
  ?, ?
</code></pre>
  <p data-lake-id="u3c106e17" id="u3c106e17"><span data-lake-id="u1c084553" id="u1c084553">​</span><br></p>
  <p data-lake-id="u24ee27e8" id="u24ee27e8"><span data-lake-id="u6dcebe67" id="u6dcebe67">大致就是基于product_type_enum，risk_case_status_enum，subject_id做了条件查询，并且基于 subject_id_enum、subject_id两个字段做了一下分组。</span></p>
  <p data-lake-id="u81f1a848" id="u81f1a848"><span data-lake-id="u6dd81204" id="u6dd81204">​</span><br></p>
  <p data-lake-id="u8ab3f586" id="u8ab3f586"><span data-lake-id="ua5ed9195" id="ua5ed9195">看了一下这条SQL的执行计划：</span></p>
  <p data-lake-id="u771fbd14" id="u771fbd14"><span data-lake-id="ua06709b2" id="ua06709b2">​</span><br></p>
  <pre lang="java"><code>
+----+-------+------------------------------+---------------------+----------------------------------------------------+                                           
| id | type  | possible_keys                | key                 | Extra                                              |                                           
+----+-------+------------------------------+---------------------+----------------------------------------------------+                                           
|  1 | range | idx_subject_product          | idx_subject_product | Using index condition; Using where; Using filesort |                                           
+----+-------+------------------------------+---------------------+----------------------------------------------------+ 
</code></pre>
  <p data-lake-id="u2150c012" id="u2150c012"><br></p>
  <p data-lake-id="u2ee7b945" id="u2ee7b945"><br></p>
  <p data-lake-id="u044c3342" id="u044c3342"><span data-lake-id="u526ffd2b" id="u526ffd2b">通过这个SQL我们可以分析出来，其实它是走了索引的，命中了idx_subject_product这个索引，其中包含了subject_id和product_type_enum字段。</span></p>
  <p data-lake-id="u2a6c2471" id="u2a6c2471"><span data-lake-id="u6903e084" id="u6903e084">​</span><br></p>
  <p data-lake-id="u00792fe6" id="u00792fe6"><span data-lake-id="u45a0f29a" id="u45a0f29a">并且这条SQL因为用到了group by所以需要进行排序，但是并没有用到索引排序，而是基于filesort进行的。</span></p>
  <p data-lake-id="ue9026b47" id="ue9026b47"><span data-lake-id="u1272ddaa" id="u1272ddaa">​</span><br></p>
  <p data-lake-id="u8bf0cb04" id="u8bf0cb04"><span data-lake-id="u5e531167" id="u5e531167">​</span><br></p>
  <p data-lake-id="uab098a3e" id="uab098a3e"><span data-lake-id="uf9e133d3" id="uf9e133d3">那么，首先想到的优化方式就是提升这个sort的性能。</span></p>
  <p data-lake-id="ud66ad148" id="ud66ad148"><span data-lake-id="ub07eadb5" id="ub07eadb5">​</span><br></p>
  <h3 data-lake-id="Qnw42" id="Qnw42"><span data-lake-id="u16714ce3" id="u16714ce3" style="color: rgba(25, 26, 31, 0.9)">问题解决</span></h3>
  <p data-lake-id="u3183ef5e" id="u3183ef5e"><br></p>
  <p data-lake-id="uae50cc21" id="uae50cc21"><span data-lake-id="u395f7e96" id="u395f7e96">想要使用到索引进行排序，就需要建立一个subject_id_enum, subject_id两个字段的联合索引，但是又要兼顾where条件的查询性能。所以也需要考虑product_type_enum，risk_case_status_enum等字段。</span></p>
  <p data-lake-id="u8fcdc0c5" id="u8fcdc0c5"><span data-lake-id="u0d48e1b0" id="u0d48e1b0">​</span><br></p>
  <p data-lake-id="uae207ab5" id="uae207ab5"><span data-lake-id="ubb23d0e2" id="ubb23d0e2">但是因为这里面risk_case_status_enum是一个常量值进行where判断的，并且根据业务分析，risk_case_status_enum = 'DRAFT'的数据量在这张表中占比非常小，所以那么就很容易想到索引该怎么创建了：</span></p>
  <p data-lake-id="ue191ed0c" id="ue191ed0c"><span data-lake-id="u41f0758e" id="u41f0758e">​</span><br></p>
  <p data-lake-id="ucc53866b" id="ucc53866b"><span data-lake-id="u84d6a78d" id="u84d6a78d">即建立一个risk_case_status_enum,subject_id_enum, subject_id三个字段的联合索引，并且顺序按照risk_case_status_enum,subject_id_enum, subject_id这样排列。</span></p>
  <p data-lake-id="ua8044e66" id="ua8044e66"><span data-lake-id="uea76db1e" id="uea76db1e">​</span><br></p>
  <p data-lake-id="uca891d67" id="uca891d67"><span data-lake-id="u868b4dd7" id="u868b4dd7">如此机能让where判断走索引，也能让排序走到索引。索引简历之后，执行计划如下：</span></p>
  <p data-lake-id="u6a92c5c6" id="u6a92c5c6"><span data-lake-id="ub9bb667d" id="ub9bb667d">​</span><br></p>
  <pre lang="java"><code>
+----+-------+-------------------------------------------------+---------------------+----------------------------------------------------+                                           
| id | type  | possible_keys                                   | key                 | Extra                                              |                                           
+----+-------+-------------------------------------------------+---------------------+----------------------------------------------------+                                           
|  1 | range | idx_subject_product ,idx_status_subject         | idx_status_subject  | Using index condition;                             |                                           
+----+-------+-------------------------------------------------+---------------------+----------------------------------------------------+ 
</code></pre>
  <p data-lake-id="u16d39291" id="u16d39291"><br></p>
  <p data-lake-id="ua31a0477" id="ua31a0477"><span data-lake-id="ua4840b92" id="ua4840b92">这样Extra中就只有</span><code data-lake-id="u6befcf92" id="u6befcf92"><span data-lake-id="u2f7b677b" id="u2f7b677b">Using index condition</span></code><span data-lake-id="uc9e06294" id="uc9e06294">了，即基于索引实现了排序。</span></p>
  <p data-lake-id="u1499a49e" id="u1499a49e"><span data-lake-id="u001c8a63" id="u001c8a63">​</span><br></p>
  <p data-lake-id="u2b8dab99" id="u2b8dab99"><span data-lake-id="u7611ac26" id="u7611ac26">并且在发布之后，不再有报警发生，问题解决。</span></p>
 </body>
</html>